package com.metacube.metaconnect.model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

public class StatusPostDAO {

	private Connection conn;

	public StatusPostDAO() {
		conn = CreateConnectionToDatabase.createConnection();
	}

	/**
	 * to get list of all posts of a user
	 * 
	 * @param userEmail
	 *            email_id of user of which all posts are to be find
	 * @return list containing all post of user
	 */
	public ArrayList getAllPosts(String userEmail) {
		ArrayList posts = new ArrayList();

		String query = "select P.post_id, P.post_content, P.time_of_post,P.email_id, U.name, U.profile_picture_url, U.gender "
				+ "FROM Posts P, userinformation U "
				+ "WHERE P.email_id=U.email_id and P.post_active=? and U.email_id=? and DATEDIFF(CURRENT_DATE, DATE(P.time_of_post)) <= ? ORDER BY P.time_of_post DESC";
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setBoolean(1, true);
			ps.setString(2, userEmail);
			ps.setInt(3, 30);
			
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				if (rs.getString("profile_picture_url") == null) {
					if (rs.getString("gender").equals("Male"))
						posts.add("Upload/male.jpg");
					else
						posts.add("Upload/female.jpg");
				} else {
					posts.add("Upload/" + rs.getString("profile_picture_url"));
				}

				posts.add(rs.getString("name"));
				posts.add(rs.getString("post_content"));
				posts.add(rs.getString("time_of_post"));
				posts.add(rs.getString("post_id"));
				posts.add(rs.getString("email_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return posts;
	}

	/**
	 * @param email
	 *            email_id of the user who post a status
	 * @param post
	 *            post content
	 * @return true if status is successfully posted else returns false
	 */
	public boolean postStatus(String email, String post) {
		String query = "insert into Posts(email_id,post_content) values(?,?)";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setString(1, email);
			ps.setString(2, post.replace("<", "&lt;").replace(">", "&gt;").replace("&","&amp;"));

			int result = ps.executeUpdate();

			if (result == 1)
				return true;
		} catch (SQLException e) {

		}
		return false;
	}

	/**
	 * @param pageCount
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public ArrayList getAllPosts() {

		ArrayList posts = new ArrayList();
		String query = "select P.post_id, P.post_content, P.time_of_post,P.email_id, U.name, U.profile_picture_url, U.gender "
				+ "FROM Posts P, userinformation U "
				+ "WHERE P.email_id=U.email_id and P.post_active=? and DATEDIFF(CURRENT_DATE, DATE(P.time_of_post)) <= ? ORDER BY P.time_of_post DESC";

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(query);

			ps.setBoolean(1, true);
			ps.setInt(2, 30);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {

				if (rs.getString("profile_picture_url") == null) {
					if (rs.getString("gender").equals("Male"))
						posts.add("Upload/male.jpg");
					else
						posts.add("Upload/female.jpg");
				} else {

					posts.add("Upload/" + rs.getString("profile_picture_url"));
				}
				posts.add(rs.getString("name"));
				posts.add(rs.getString("post_content"));
				posts.add(rs.getString("time_of_post"));
				posts.add(rs.getString("post_id"));
				posts.add(rs.getString("email_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return posts;
	}

	/**
	 * delete a posts
	 * 
	 * @param post_id
	 *            post_id of post which is to be deleted
	 */
	public boolean deletePost(int post_id) {
		String query = "update posts set post_active =? where post_id=? ";

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(query);

			ps.setBoolean(1, false);
			ps.setInt(2, post_id);

			int result = ps.executeUpdate();
			if (result == 1)
				return true;
		} catch (SQLException e) {

		}
		return false;
	}
}
